import frappe
from frappe.desk.reportview import get_filters_cond
from frappe.utils import nowdate, nowtime
from erpnext.controllers.queries import get_fields
import json



def get_cached_value(key, settings='Light Mes Settings'):
    def get_value():
        return frappe.db.get_single_value(settings, key)

    return frappe.cache().get_value(key, get_value)

@frappe.whitelist()
def get_employee_list(workstation):
    emp_list = frappe.get_all("Employee Group",
        filters = {"workstation": workstation},
        fields = '`tabEmployee Group Table`.employee'
    )
    return emp_list

# searches for active employees
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def employee_query(doctype, txt, searchfield, start, page_len, filters):
	doctype = "Employee"
	conditions = []
	fields = get_fields(doctype, ["name", "employee_name"])

	return frappe.db.sql(
		"""select {fields} from `tabEmployee`
		where status in ('Active', 'Suspended')
			and docstatus < 2
			and ({key} like %(txt)s
				or employee_name like %(txt)s)
			{fcond}
		order by
			(case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
			(case when locate(%(_txt)s, employee_name) > 0 then locate(%(_txt)s, employee_name) else 99999 end),
			idx desc,
			name, employee_name
		limit %(page_len)s offset %(start)s""".format(
			**{
				"fields": ", ".join(fields),
				"key": searchfield,
				"fcond": get_filters_cond(doctype, filters, conditions),
				#"mcond": get_match_cond(doctype), 去掉user permission 过滤条件限制
			}
		),
		{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
	)


@frappe.whitelist()
def generate_tracking_numbers(work_order, count=1, arg={}):
    """
    为工单生成多个追踪号
    """
    count = int(count)
    current_tracking_number = frappe.db.get_value("Tracking Number",
        {
            "work_order": work_order,
            "is_parent_tracking_number": 0
        },
        "max(name)"
    )
    start_number = 0 if not current_tracking_number else int(current_tracking_number[-3:])
    if (start_number + count)>1000:
        frappe.throw(_("{0} over max allowed 1000").format((start_number + count)))
    tracking_numbers = []    
    for i in range(1, count + 1):
        doc_dict = {
            "doctype": "Tracking Number",
            "work_order": work_order,
            "tracking_number": f"{work_order}-{str(start_number+i).zfill(3)}"
        }
        doc_dict.update(arg)
        doc = frappe.get_doc(doc_dict).insert()
        tracking_numbers.append(doc.name)

    return tracking_numbers

@frappe.whitelist()
def generate_combined_tracking_numbers(work_order, tracking_details):
    """
    为尚未生成拼卷号，新扫描的2或3个的分条卷生成拼卷追踪号,
    合并后的追踪号工单号-Lxxx,加一个L前缀以区别于
    """
    
    tracking_details = json.loads(tracking_details)
    child_numbers = {
        r.get("tracking_number") for r in tracking_details if not r.get("parent_tracking_number")
    }
    if len(child_numbers)>3 or len(child_numbers)<2:
        frappe.throw("仅限两卷或三卷拼成一卷")

    current_tracking_number = frappe.db.get_value("Tracking Number",
        {
            "work_order": work_order,
            "is_parent_tracking_number": 1
        },
        "max(name)"
    )
    start_number = 0 if not current_tracking_number else int(current_tracking_number[-3:])
    
    if (start_number + 1) > 1000:
        frappe.throw(_("{0} over max allowed 1000").format((start_number + count)))

    doc = frappe.get_doc({
        "doctype": "Tracking Number",
        "work_order": work_order,
        "is_parent_tracking_number": 1,
        # 工单与流水号之间的连接符由- 改为了 . , 以示两者的区别
        "tracking_number": f"{work_order}.{str(start_number+1).zfill(3)}"
    }).insert()
    
    frappe.db.set_value("Tracking Number", 
        {
            "name": ("in", child_numbers)
        }, 
        "parent_tracking_number", doc.name
    )
    for row in tracking_details:        
        if not row.get("parent_tracking_number"):
            row["parent_tracking_number"] = doc.name
            
    return tracking_details

@frappe.whitelist()
def is_work_order_first_operation(work_order, operation_id):
    work_order_doc = frappe.get_doc("Work Order", work_order)
    if work_order_doc.operations:
        return work_order_doc.operations[0].name == operation_id

@frappe.whitelist()
def generate_tracking_number_qr_code(tracking_number):
    import json, hashlib
    from erpnext_qrcode.qrcode_generator import generate_qr_code

    return generate_qr_code(json.dumps(tracking_number))

@frappe.whitelist()
def get_child_tracking_number_str(tracking_number):
    child_trackings = frappe.get_all("Tracking Number",
        filters = {
            "parent_tracking_number": tracking_number
        }, 
        pluck="name"
    )
    tracking_number_str = ";".join(d[-3:] for d in child_trackings)
    
    return f"({tracking_number_str})"

@frappe.whitelist()
def get_workstation_employees(workstation):
    """
    按分派的班次获取当前班次的员工
    """

    workstation_doc = frappe.get_doc("Workstation", workstation)
    if workstation_doc.employees and workstation_doc.has_shift_type:
        shift_assignment = frappe.qb.DocType("Shift Assignment")
        shift_type = frappe.qb.DocType("Shift Type")
        cur_date = nowdate()
        cur_time = nowtime()
        employees = frappe.qb.from_(shift_assignment
        ).join(shift_type
        ).on(shift_assignment.shift_type == shift_type.name
        ).where(
            (shift_assignment.docstatus==1) &
            (shift_assignment.employee.isin([r.employee for r in workstation_doc.employees])) &
            (shift_assignment.start_date <= cur_date) &
            (shift_assignment.end_date >= cur_date) &
            (
                ((shift_type.start_time < shift_type.end_time) & (shift_type.start_time <= cur_time) &
                    (shift_type.end_time >= cur_time)) |
                ((shift_type.start_time > shift_type.end_time) & (shift_type.start_time <= cur_time))
            )
        ).select(shift_assignment.employee
        ).run(pluck=1)
        workstation_doc.employees =[r for r in workstation_doc.employees if r.employee in employees]
    return workstation_doc
